数据库考试 知识点汇总
一、数据库系统概述
1.1 数据库系统阶段数据管理的特点 (P5)
| 特点 | 说明 |
|---|---|
| 结构化 | 数据及其联系的集合，整体结构化 |
| 共享性高、冗余度低 | 多用户、多应用共享数据 |
| 数据独立性高 | 物理独立性和逻辑独立性 |
| 统一管理和控制 | 由DBMS统一管理，包括安全性、完整性、并发控制、恢复等 |
1.2 数据库系统的组成 (P7)
四部分：硬件系统、软件系统（DBMS、OS等）、数据库（DB）、数据库用户（DBA、开发者、终端用户）。
1.3 MySQL自带的系统数据库
| 数据库名 | 作用 |
|---|---|
| mysql | 存储用户账户、权限等系统信息 |
| information_schema | 存储所有数据库的元数据（表名、列名等） |
| performance_schema | 存储性能监控数据 |
1.4 数据库文件类型
数据库在物理层面由多种文件组成：
| 文件类型 | 扩展名 | 说明 |
|---|---|---|
| 主数据文件 | .mdf | 包含数据库的启动信息和主要数据，每个数据库有且只有一个 |
| 次要数据文件 | .ndf | 存储主数据文件未包含的数据，可以有多个或没有 |
| 日志文件 | .ldf | 记录所有事务和数据库修改，用于恢复，至少有一个 |
各文件的作用：
- 主数据文件 (Primary Data File)
- 数据库的起点，包含数据库的启动信息
- 存储系统表和用户数据
- 每个数据库必须有且只有一个主数据文件
- 次要数据文件 (Secondary Data File)
- 用于扩展存储空间
- 可以将数据分布到多个磁盘上，提高I/O性能
- 可选，数量不限
- 日志文件 (Transaction Log File)
- 记录所有数据库修改操作（事务日志）
- 用于数据库恢复和事务回滚
- 支持事务的原子性和持久性
- 每个数据库至少需要一个日志文件
MySQL的文件结构：
| 文件类型 | 说明 |
|---|---|
| .frm | 表结构定义文件 |
| .ibd | InnoDB表的数据和索引文件 |
| .MYD | MyISAM表的数据文件 |
| .MYI | MyISAM表的索引文件 |
| redo log | 重做日志，用于崩溃恢复 |
| undo log | 回滚日志，用于事务回滚 |
| binlog | 二进制日志，用于主从复制和数据恢复 |
二、三级模式与二级映像 (P11)
2.1 三级模式结构
| 模式 | 数量 | 说明 |
|---|---|---|
| 外模式 | 可有多个 | 用户视图，描述用户看到的数据结构 |
| 模式 | 只有一个 | 全局逻辑结构，是所有用户的公共数据视图 |
| 内模式 | 只有一个 | 存储模式，描述数据的物理结构和存储方式 |
2.2 二级映像与数据独立性
| 映像 | 作用 | 独立性类型 |
|---|---|---|
| 外模式/模式映像 | 定义外模式与模式之间的对应关系 | 逻辑独立性 |
| 模式/内模式映像 | 定义模式与内模式之间的对应关系 | 物理独立性 |
核心：通过两层映像实现了数据与程序之间的解耦
三、实体联系与数据模型 (P14)
3.1 联系的定义与分类
定义：现实世界中事物内部或事物之间的联系在信息世界中的反映（即实体集之间的逻辑关系）。
分类：
- 一对一 (1:1)：如班长与班级
- 一对多 (1:n)：如班级与学生
- 多对多 (m:n)：如学生与课程（需通过中间表实现）
四、关系的完整性 (P32)
| 完整性类型 | 定义 | 作用对象 |
|---|---|---|
| 实体完整性 | 主码的值不能为空或部分为空 | 主码 |
| 参照完整性 | 外码值必须是主表中存在的值或为NULL | 表间关系 |
| 用户自定义完整性 | 反映具体应用的语义约束条件 | 具体应用 |
五、SQL数据定义语言
5.1 数据库操作 (P60)
-- 创建数据库
CREATE DATABASE 数据库名;
-- 修改数据库
ALTER DATABASE 数据库名 ...;
-- 删除数据库（删除整个数据库及其包含的所有表）
DROP DATABASE 数据库名;
-- 查看当前操作的数据库
SELECT DATABASE();
5.2 约束类型
唯一约束 UNIQUE (P70)
| 特性 | 说明 |
|---|---|
| 允许NULL | 最多只允许出现一个NULL值 |
| 约束类型 | 既可作为表约束，又可作为列约束 |
| 数量限制 | 一个表可以有多个UNIQUE约束 |
| 字段范围 | 可定义在多个字段上 |
主码约束 PRIMARY KEY (P71)
| 特性 | 说明 |
|---|---|
| 作用 | 唯一标识每一行记录 |
| NULL值 | 不能为NULL，不能重复 |
| 数量限制 | 一个表只能定义一个PRIMARY KEY |
| 语法 | <字段名> <数据类型> PRIMARY KEY |
外码约束 FOREIGN KEY (P72)
| 特性 | 说明 |
|---|---|
| 作用 | 在两个表之间建立连接，保证参照完整性 |
| 值要求 | 必须是主表中存在的主码值，或者为NULL |
| 级联选项 | ON DELETE CASCADE可自动删除子表相关记录 |
注意：外键建在"多"的一方，指向"一"的一方
非空约束 NOT NULL
要求某列的值不能为空。
检查约束 CHECK
CHECK (Price > 0) -- 价格必须大于0
六、SQL数据操作语言
6.1 数据操作语句
添加数据 (P79)：
INSERT INTO 表名(字段1, 字段2...) VALUES(值1, 值2...);
-- 只有当插入全部字段且顺序一致时，才可省略字段名
-- 插入多条数据
INSERT INTO 表名(字段列表) VALUES
(值1, 值2...),
(值1, 值2...);
修改数据 (P80)：
UPDATE 表名 SET 字段名 = 新值 WHERE 条件;
-- ⚠️注意：不加WHERE会修改全表！
删除数据 (P81)：
DELETE FROM 表名 WHERE 条件;
-- ⚠️注意：不加WHERE会清空全表！
-- 快速清空表（不记录日志，速度更快）
TRUNCATE TABLE 表名;
DELETE vs TRUNCATE：
- DELETE：逐行删除，记录日志，可回滚
- TRUNCATE：直接清空，不记日志，不可回滚，速度快
七、SQL查询语句 (P85-95)
7.1 查询语法结构
SELECT [DISTINCT] 字段列表
FROM 表名
[WHERE 条件]
[GROUP BY 分组字段]
[HAVING 分组后条件] -- 必须在GROUP BY之后
[ORDER BY 排序字段 [ASC|DESC]]
[LIMIT n]; -- 限制返回前n行
7.2 条件查询示例 (P88)
- 比较运算：
score >= 90 - 范围查询：
BETWEEN 30 AND 40（包含边界） - 集合查询：
NOT IN ('c4', 'c6') - 模糊查询：
LIKE '%程序%'（%代表任意个字符，_代表一个字符） - 正则匹配：
REGEXP '正则表达式' - 空值判断：
IS NULL/IS NOT NULL（不能用 = NULL）
-- 比较运算符
SELECT * FROM sc WHERE score >= 90;
-- AND条件
SELECT tno AS 教师号, tn AS 姓名, prof AS 职称
FROM t
WHERE age >= 30 AND age <= 40;
-- BETWEEN...AND
SELECT cno, cn, ct
FROM c
WHERE ct BETWEEN 30 AND 40;
-- NOT IN
SELECT sno, cno, score
FROM sc
WHERE cno NOT IN ('c4', 'c6');
-- LIKE模糊查询
SELECT cno AS 课程号, cn AS 课程名, ct AS 课时
FROM c
WHERE cn LIKE '%程序%';
注意：任何与NULL的比较运算结果都是NULL
7.3 分组查询示例 (P95)
聚合函数：COUNT(), SUM(), AVG(), MAX(), MIN()
MIN vs LEAST：
MIN()：聚合函数，用于求一列的最小值LEAST()：普通函数，用于求参数列表中的最小值，如LEAST(10, 2, 5)返回 2
重点区别：WHERE 筛选行（分组前）；HAVING 筛选组（分组后）
-- 统计每门课程选课人数
SELECT cno AS 课程号, COUNT(*) AS 选课人数
FROM sc
GROUP BY cno;
-- HAVING过滤分组
SELECT sno AS 学号, COUNT(*) AS 选课门数
FROM sc
GROUP BY sno
HAVING COUNT(*) >= 3;
-- 排序（DESC降序，ASC升序默认可不写）
SELECT sno, cno, score
FROM sc
WHERE sno = 's2'
ORDER BY score DESC;
-- LIMIT限制行数
SELECT * FROM tb_student LIMIT 30; -- 显示前30行
7.4 字段别名
SELECT cn AS 课程名 FROM c;
SELECT cn 课程名 FROM c; -- AS可以省略
7.5 合并查询结果
| 关键字 | 说明 |
|---|---|
| UNION | 合并结果集，自动去除重复记录 |
| UNION ALL | 合并结果集，保留所有记录（含重复） |
7.6 运算表达式
SELECT 0 OR (4>3); -- 结果为 1（4>3为真即1，0 OR 1 = 1）
SELECT 20/5*2; -- 结果为 8.0000（MySQL除法默认转浮点）
八、连接查询与子查询
8.1 连接查询 vs 子查询
子查询是嵌套的SELECT语句，从内向外逐层执行，结果通常来自一个表，适用于条件值来自其他表的场景，可能执行较慢。
连接查询是多表通过JOIN连接，同时处理多表，结果可来自多个表，适用于需要显示多表字段的场景，通常性能更高。
注意：子查询不仅可以在WHERE子句中，也可以在FROM子句、SELECT列表中使用。
| 比较项 | 子查询 | 连接查询 |
|---|---|---|
| 结构 | 嵌套的SELECT语句 | 多表通过JOIN连接 |
| 执行方式 | 从内向外逐层执行 | 同时处理多表 |
| 结果来源 | 通常来自一个表 | 可来自多个表 |
| 适用场景 | 条件值来自其他表 | 需要显示多表字段 |
| 性能 | 可能较慢（多次执行） | 通常更高效 |
8.2 INNER JOIN vs LEFT JOIN
INNER JOIN是内连接，只返回两表中匹配的记录，不匹配的记录不显示。
LEFT JOIN是左外连接，返回左表所有记录，如果右表没有匹配的记录则显示为NULL。
| 类型 | 说明 | 结果 |
|---|---|---|
| INNER JOIN | 内连接 | 只返回两表中匹配的记录 |
| LEFT JOIN | 左外连接 | 返回左表所有记录，右表无匹配则为NULL |
示例：
-- INNER JOIN：只显示有选课记录的学生
SELECT s.sno, s.sn, sc.cno
FROM s INNER JOIN sc ON s.sno = sc.sno;
-- LEFT JOIN：显示所有学生，没选课的也显示（课程号为NULL）
SELECT s.sno, s.sn, sc.cno
FROM s LEFT JOIN sc ON s.sno = sc.sno;
九、视图 (P115-122)
9.1 视图的创建与使用
-- 创建视图（需要CREATE VIEW权限和相关SELECT权限）
CREATE VIEW s_view
AS SELECT * FROM s
WHERE dept = '信息学院';
-- 带检查选项的视图
CREATE VIEW view_name
AS SELECT ...
WITH CHECK OPTION; -- 更新数据时检查是否满足视图条件
9.2 视图的特点
数据表是实际存储数据的物理结构，占用存储空间，存储真实数据，可直接修改。
视图是虚拟表，只存储查询定义的SQL语句，不存储实际数据，数据动态从基表获取，更新有限制条件。
选择建议：需要永久存储数据用表，需要简化查询、限制访问或提供不同视角时用视图。
| 比较项 | 数据表 | 视图 |
|---|---|---|
| 本质 | 实际存储数据的物理结构 | 虚拟表，只存储查询定义 |
| 存储 | 占用物理存储空间 | 不存储数据，只存SQL语句 |
| 数据 | 真实数据 | 动态从基表获取 |
| 更新 | 直接修改 | 有限制条件 |
| 用途 | 永久存储数据 | 简化查询、控制访问权限 |
- 需要存储数据 → 表
- 简化复杂查询、限制数据访问、提供不同视角 → 视图
9.3 视图更新
可使用 INSERT、UPDATE、DELETE 语句更新视图数据
WITH CHECK OPTION 参数会限制更新操作必须满足视图定义条件
视图定义中若包含以下内容，则不能通过视图修改基表数据：
- GROUP BY 子句
- 聚合函数（SUM、COUNT等）
- DISTINCT 关键字
- UNION 操作
9.4 查看视图
DESC 视图名; -- 查看视图结构
SHOW CREATE VIEW 视图名; -- 查看视图定义
注意：DESC 和 SHOW 查看视图的显示结果不相同
十、索引
索引是提高数据库查询效率的数据结构，类似于书籍的目录。
10.1 索引操作
-- 创建索引
CREATE INDEX idx_title ON Book(Title);
-- 或者
ALTER TABLE Book ADD INDEX idx_title(Title);
-- 创建唯一索引
CREATE UNIQUE INDEX idx_isbn ON Book(ISBN);
-- 创建复合索引（多列索引）
CREATE INDEX idx_name_age ON Student(name, age);
-- 查看索引
SHOW INDEX FROM 表名;
-- 删除索引
DROP INDEX idx_title ON Book;
10.2 聚集索引与非聚集索引
| 特性 | 聚集索引 (Clustered Index) | 非聚集索引 (Non-clustered Index) |
|---|---|---|
| 物理存储 | 决定数据在磁盘上的物理存储顺序 | 不影响数据的物理存储顺序 |
| 数量限制 | 一个表只能有一个 | 一个表可以有多个 |
| 叶子节点 | 存储实际的数据行 | 存储指向数据行的指针 |
| 查询效率 | 范围查询效率高 | 可能需要"回表"操作 |
| 默认创建 | 主键默认创建聚集索引 | 普通索引默认为非聚集索引 |
聚集索引 (Clustered Index)：
- 表中数据按照聚集索引的顺序物理存储
- 就像字典按拼音顺序排列，数据本身就是有序的
- 一个表只能有一个聚集索引（因为数据只能有一种物理排列方式）
- InnoDB中，主键默认就是聚集索引
非聚集索引 (Non-clustered Index)：
- 索引和数据分开存储
- 就像书后的索引，索引指向数据的位置
- 查询时先找索引，再根据指针找数据（可能需要"回表"）
- 一个表可以有多个非聚集索引
10.3 索引的优缺点
优点：
- 大大加快数据检索速度
- 加速表与表之间的连接
- 使用分组和排序时，可以显著减少时间
缺点：
- 占用额外的存储空间
- 增删改操作时需要维护索引，降低写入速度
- 创建和维护索引需要时间
10.4 索引使用原则
| 适合创建索引 | 不适合创建索引 |
|---|---|
| 经常用于WHERE条件的列 | 很少查询的列 |
| 经常用于连接的列（外键） | 数据值很少的列（如性别） |
| 经常需要排序的列 | 频繁更新的列 |
| 主键列 | 数据量很小的表 |
十一、权限管理 (P147)
11.1 权限授予 GRANT
GRANT 权限名称 [(字段列表)]
ON 授权级别及对象
TO '用户名'@'主机信息'
[WITH GRANT OPTION]; -- 允许被授权者继续授权给其他用户
11.2 权限回收 REVOKE
REVOKE 权限名称
ON 授权级别及对象
FROM '用户名'@'主机信息';
11.3 MySQL用户管理基本操作
| 操作 | 说明 |
|---|---|
| 创建用户 | CREATE USER '用户名'@'主机' IDENTIFIED BY '密码'; |
| 删除用户 | DROP USER '用户名'@'主机'; |
| 修改密码 | ALTER USER '用户名'@'主机' IDENTIFIED BY '新密码'; |
| 授予权限 | GRANT 权限 ON 对象 TO 用户; |
| 回收权限 | REVOKE 权限 ON 对象 FROM 用户; |
| 查看权限 | SHOW GRANTS FOR '用户名'@'主机'; |
11.4 MySQL中可授予的权限类型
- 数据操作权限：SELECT, INSERT, UPDATE, DELETE（针对表数据）
- 数据定义权限：CREATE, ALTER, DROP, INDEX（针对库表结构）
- 管理权限：CREATE USER, GRANT OPTION, SHUTDOWN, ALL PRIVILEGES（针对服务器管理）
十二、事务 (P156)
12.1 事务的ACID特性
- A - 原子性 (Atomicity)：事务是不可分割的最小工作单位，要么全部成功，要么全部失败回滚
- C - 一致性 (Consistency)：事务执行前后，数据库必须从一个一致性状态变到另一个一致性状态
- I - 隔离性 (Isolation)：并发执行的事务之间互不干扰，一个事务的中间状态对其他事务不可见
- D - 持久性 (Durability)：事务一旦提交，对数据的修改就是永久的，即使系统故障也不会丢失
12.2 事务的标准状态
活动的(Active)、部分提交的、失败的(Failed)、中止的、提交的(Committed)
注意："挂起的(Pending)"不是事务的标准状态
12.3 并发问题
| 问题 | 说明 |
|---|---|
| 丢失更新 | 两事务同时读取同一数据并修改，后提交的覆盖了先提交的修改 |
| 脏读 | 读取到另一事务尚未提交的数据 |
| 不可重复读 | 同一事务内两次读取结果不同 |
| 幻读 | 同一事务内两次查询记录数不同 |
丢失更新的解决方法：提高事务隔离级别（如可重复读）或使用锁机制
12.4 事务隔离级别
| 隔离级别 | 说明 |
|---|---|
| READ UNCOMMITTED | 读未提交 |
| READ COMMITTED | 读已提交 |
| REPEATABLE READ | 可重复读（MySQL默认） |
| SERIALIZABLE | 串行化 |
十三、数据库备份与恢复 (P173)
13.1 备份类型
| 备份类型 | 说明 |
|---|---|
| 完整备份 | 备份整个数据库 |
| 差异备份 | 备份上次完整备份后的所有变化 |
| 增量备份 | 备份上次任意备份后的变化 |
13.2 备份策略
- 备份内容：数据、日志、代码、服务器配置文件等
- 系统数据库：修改后立即备份
- 用户数据库：周期性备份
- 恢复基础：日志文件（记录数据库的所有变更操作）
13.3 数据导入导出 (P184)
# 使用mysqlimport导入文件（是LOAD DATA INFILE的命令行接口）
mysqlimport [选项] 数据库名 文件名
十四、数据库设计范式
14.1 三大范式
- 1NF：属性不可分（原子性）
- 2NF：消除了非主属性对码的部分函数依赖（即：非主属性必须完全依赖于主键）
- 3NF：消除了非主属性对码的传递函数依赖
第二范式 (2NF)：在1NF基础上，非主属性完全函数依赖于主码，即消除非主属性对主码的部分函数依赖。解决的问题：减少数据冗余，避免插入异常、删除异常、更新异常。
| 项目 | 说明 |
|---|---|
| 定义 | 在1NF基础上，非主属性完全函数依赖于主码（消除部分依赖） |
| 解决问题 | 消除非主属性对主码的部分函数依赖 |
| 作用 | 减少数据冗余，避免更新异常 |
第三范式 (3NF)：在2NF基础上，消除非主属性对主码的传递函数依赖（即不存在 A→B→C 的传递依赖）。作用：进一步减少数据冗余，提高数据一致性，便于维护。
| 项目 | 说明 |
|---|---|
| 定义 | 在2NF基础上，非主属性不传递依赖于主码（消除传递依赖） |
| 条件 | 每个非主属性都直接依赖于主码，不存在 A→B→C 的传递依赖 |
| 作用 | 进一步减少数据冗余，提高数据一致性，便于维护 |
判断技巧：如果主码是单字段（如学号），不存在组合主键，自然不存在部分依赖，所以自动满足2NF。
14.2 逻辑结构设计 (P234)
E-R图转换为关系模式的原则：
- 实体转换为表： 属性即列，标识符即主码。
- 1:1 联系： 可以转换为独立的关系，也可以与任意一端实体对应的关系模式合并（通常合并到访问更频繁的那一端）。
- 1:n 联系： 将“1”方的主码纳入“n”方作为外码。
- 联系本身的属性也放在“n”方。
- m:n 联系： 必须转换为一个新的独立关系模式（新表）。
- 新表的属性 = 双方实体的主码 + 联系本身的属性。
- 新表的主码 = 双方主码的组合。
十五、MySQL编程 (P257)
15.1 注释方式
-- 单行注释（注意--后有空格）
# 单行注释
/*
多行注释
*/
15.2 SQL语句结束符
| 符号 | 作用 |
|---|---|
; |
标准结束符 |
\g |
等同于分号 |
\G |
垂直显示结果 |
注意：. 不能用作SQL语句结束符
15.3 变量类型
| 变量类型 | 前缀 | 说明 |
|---|---|---|
| 局部变量 | 无或@ |
需用DECLARE声明，在BEGIN...END中使用 |
| 用户会话变量 | @ |
当前会话有效 |
| 系统变量 | @@ |
MySQL自动创建，分为全局(Global)和会话(Session)两类 |
15.4 常用函数
| 函数 | 作用 |
|---|---|
CONCAT() |
将多个参数连接成一个字符串 |
IFNULL(字段, '默认值') |
空值替换 |
RAND() |
返回0到1之间的随机浮点数 |
十六、存储过程与触发器
16.1 存储过程 (P280)
-- 创建存储过程
DELIMITER &&
CREATE PROCEDURE 过程名([IN/OUT/INOUT 参数名 参数类型])
BEGIN
-- SQL语句
END &&
DELIMITER ;
-- 调用存储过程
CALL 过程名([参数]);
16.2 存储函数
-- 创建存储函数
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
-- 必须有RETURN语句
RETURN 值;
END;
存储过程与存储函数的区别：
| 比较项 | 存储过程 | 存储函数 |
|---|---|---|
| 参数类型 | 支持 IN、OUT、INOUT | 通常只支持 IN |
| 返回值 | 可以不返回 | 必须返回一个值 |
| 调用方式 | 使用 CALL 语句独立调用 | 作为表达式在SQL中调用（如 SELECT func()） |
16.3 触发器 (P308)
| 类型 | 执行时机 |
|---|---|
| BEFORE | 在INSERT/UPDATE/DELETE之前执行 |
| AFTER | 在INSERT/UPDATE/DELETE之后执行 |
CREATE TRIGGER 触发器名
BEFORE|AFTER INSERT|UPDATE|DELETE
ON 表名 FOR EACH ROW
BEGIN
-- 触发器逻辑
END;
注意：
- 触发事件只包括 INSERT、UPDATE、DELETE，不包含 SELECT
- 触发器是自动触发的，不能用 CALL 调用
触发器的作用：
- 强制实施复杂的业务规则和约束（安全性）
- 实现级联更新或删除（完整性）
- 跟踪数据变化，记录审计日志
- 在写入数据前自动进行数据校验或转换
十七、Python数据库访问 (P323)
- Python所有数据库接口程序遵守 Python DB API 规范
- MySQL常用连接库：
mysql-connector-python、pymysql
import pymysql
# 连接数据库
conn = pymysql.connect(host='localhost', user='root',
password='pwd', database='db')
cursor = conn.cursor() # 获取cursor对象
cursor.execute("SELECT * FROM table_name") # 执行SQL语句
results = cursor.fetchall() # 获取结果
conn.close()
十八、数据库系统 vs 文件系统
文件系统中数据记录内有结构但整体无结构，共享性差、冗余度大，数据独立性差，由应用程序管理和控制数据。
数据库系统中数据整体结构化，共享性高、冗余度低，数据独立性高，由DBMS统一管理并提供安全性、完整性、并发控制等功能。
| 比较项 | 文件系统 | 数据库系统 |
|---|---|---|
| 数据结构 | 记录内有结构，整体无结构 | 整体结构化 |
| 数据共享 | 共享性差，冗余度大 | 共享性高，冗余度低 |
| 数据独立性 | 独立性差 | 独立性高 |
| 数据管理 | 由应用程序管理 | 由DBMS统一管理 |
| 数据控制 | 应用程序控制 | DBMS提供安全性、完整性、并发控制 |
💬 评论